package com.apes.fn.tms.repository;

import com.apes.fn.tms.model.Site;
import com.apes.framework.jpa.repository.CustomRepository;
import com.apes.scm.masterdata.stock.model.Location;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.util.List;
import java.util.Map;

public interface SiteRepository extends CustomRepository<Site, String> {

    @Query("SELECT T FROM Site T WHERE 1 = 1 and (T.id = :id or T.name like %:value%) ")
    Page<Site> find(@Param("id") String id, @Param("value") String value, Pageable pageable);
	
	@Query(value = "SELECT * FROM TMS_SITE T WHERE EXISTS (SELECT 1 " +
		"          FROM MAS_LOCATION " +
		"         WHERE USAGE_ID = '1' " +
		"           AND ID IN (:locationId) " +
		"           AND PHY_DEPOT_ID = T.PHY_DEPOT_ID) AND ROWNUM = 1 ", nativeQuery = true)
    Site oldQrySite(@Param("locationId") List<String> locationId);

	@Query(value = "SELECT A.ID, A.NAME, A.PHY_DEPOT_ID, B.NAME PHY_DEPOT_NAME, B.ADDRESS, B.ORIGIN " +
			"  FROM TMS_SITE A, MAS_PHY_DEPOT B " +
			" WHERE A.PHY_DEPOT_ID = B.ID " +
			"   AND A.ID = :site_id ", nativeQuery = true)
	Map oldPhyDepotAddress(@Param("site_id") String site_id);

	Site findFirstByPhyDepotId(@Param("DepotId") String DepotId);
}
